package com.ctshk.rpc.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ctshk.rpc.system.entity.SysDepartment;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 组织架构 Mapper 接口
 * </p>
 *
 * @author 谢诗宏
 * @since 2020-12-22
 */
public interface SysDepartmentMapper extends BaseMapper<SysDepartment> {

    /**
     * 根据parentId获取所有子节点-未包含parentId自己
     * @param parentId
     * @return
     */
    @Select("SELECT * FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.STATUS, t1.sort," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT id, parent_id, title, STATUS, sort FROM  t_sys_department t WHERE t.is_deleted = 0 ORDER BY parent_id, id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<SysDepartment> selectChildren(@Param("parentId") long parentId);

    /**
     * 根据parentId获取所有子节点id-未包含parentId自己
     * @param parentId
     * @return
     */
    @Select("SELECT id FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.STATUS, t1.sort," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT id, parent_id, title, STATUS, sort FROM  t_sys_department t WHERE t.is_deleted = 0 ORDER BY parent_id, id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<Long> selectChildrenIds(@Param("parentId") long parentId);

    /**
     * 根据deptId获取所有父节点-包含当前deptId
     * @param deptId
     * @return
     */
    @Select("SELECT" +
            " T2.id," +
            " T2.title," +
            " T2.parent_id," +
            " T1.LEVEL " +
            "FROM" +
            " (" +
            " SELECT" +
            "  @r AS _id," +
            "  ( SELECT @r := parent_id FROM  t_sys_department WHERE id = _id ) AS parent_id," +
            "  @l := @l + 1 AS LEVEL " +
            " FROM" +
            "  ( SELECT @r := #{deptId}, @l := 0 ) vars," +
            "   t_sys_department h " +
            " WHERE" +
            "  parent_id IS NOT NULL " +
            " ) T1" +
            " JOIN  t_sys_department T2 ON T1._id = T2.id" +
            " ORDER BY" +
            " T1.LEVEL")
    List<SysDepartment> selectParents(@Param("deptId") long deptId);

    /**
     * 根据deptId获取所有父节点id-包含当前deptId
     * @param deptId
     * @return
     */
    @Select("SELECT" +
            " T2.id " +
            "FROM" +
            " (" +
            " SELECT" +
            "  @r AS _id," +
            "  ( SELECT @r := parent_id FROM  t_sys_department WHERE id = _id ) AS parent_id," +
            "  @l := @l + 1 AS LEVEL " +
            " FROM" +
            "  ( SELECT @r := #{deptId}, @l := 0 ) vars," +
            "   t_sys_department h " +
            " WHERE" +
            "  parent_id IS NOT NULL " +
            " ) T1" +
            " JOIN  t_sys_department T2 ON T1._id = T2.id" +
            " ORDER BY" +
            " T1.LEVEL")
    List<Long> selectParentIds(@Param("deptId") long deptId);

    @Select("select max(dept_number) + 1 from t_sys_department where LENGTH(dept_number) = LENGTH(#{deptNumber}) + 3")
    String selectNextDeptNumber(@Param("deptNumber") String deptNumber);
}
